SQL Server DBAs routinely rely on automation: SQL Server Agent jobs, maintenance plans, refresh processes, restore scripts, replication cleanup, CDC operations, cross-database modules, and temporary objects. These workflows are often trusted because they’re normal – and that’s precisely why they deserve more attention.
The goal of this article is not to provide exploit recipes. Instead, it’s to help DBAs answer a practical question: how do I know whether my SQL Server instances are exposed to this kind of risk, what should I monitor, and what should I change when I find a problem?
Working assumption
Every section of this article uses the same defensive pattern: identify the trust boundary, look for risky signs, monitor the right events and catalog state, then apply a mitigation that reduces privilege, removes ambiguity, or prevents untrusted code from executing under a privileged context.
This article is part of Fabiano Amorim’s complete guide to SQL Server security on Simple Talk.
MSDB and SQL Server Agent: the operational trust boundary
DBAs sometimes treat msdb as just another system database, but it’s not. In fact, msdb is the operational control plane for a SQL Server instance.
It stores SQL Server Agent jobs, schedules, job steps, operators, alerts, proxy configuration, credentials, Database Mail metadata, backup and restore history, SSIS-related information, and maintenance plan metadata.
Why ‘normal’ SQL Server Agent permissions can create an attack path
A user who can alter the wrong object in msdb does not necessarily need to be sysadmin to create risk. The dangerous pattern is indirect execution: a lower-privileged principal modifies something that a higher-privileged job later executes. The attacker, or accidental misconfiguration, doesn’t need to run the payload directly – the next scheduled job run does it.
This is especially important when privileged maintenance jobs are owned by sa, run under the SQL Server Agent service account, or use proxies with access outside the database engine. A job step that looks like routine maintenance may become a server-level execution path if an untrusted principal can modify the job, a stored procedure the job calls, a table the job reads, or a proxy the job uses.
DBA lens
The question is not only ‘who can run this job?’ but also ‘who can modify anything this job trusts?’. That includes the job owner, job steps, schedules, proxies, credentials, called procedures, tables used as queues, and triggers on objects touched by the job.
What are the signs a DBA should look for?
- Application service accounts, developer logins, vendor accounts, or reporting users in
SQLAgentUserRole,SQLAgentReaderRole, orSQLAgentOperatorRole.
- Non-administrative accounts granted
db_owner,db_ddladmin,db_securityadmin, or broad explicit DDL (data definition language) permissions inmsdb.
- SQL Server Agent jobs owned by individual users rather than
sa, a dedicated job-owner login, or an approved administrative group.
- Job steps that run CmdExec, PowerShell, SSIS, ActiveX, or operating-system-facing actions without an approved proxy model.
- Job definitions or schedules modified outside an approved maintenance window.
- Stored procedures in
msdb, or in user databases, called by privileged jobs and writable by non-privileged users.
- Maintenance jobs that read work items from user-editable tables, predictable global temporary tables, or queues with weak ownership controls.
What to monitor and how
SQL Agent role membership in msdb
Start with a daily inventory of the fixed SQL Server Agent roles. This should be an allow-list control, not a passive report. Any account outside the expected DBA or automation group should be investigated.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
USE msdb; GO SELECT roles.name AS agent_role, members.name AS member_name, members.type_desc AS member_type FROM sys.database_role_members AS drm JOIN sys.database_principals AS roles ON roles.principal_id = drm.role_principal_id JOIN sys.database_principals AS members ON members.principal_id = drm.member_principal_id WHERE roles.name IN ( N'SQLAgentUserRole', N'SQLAgentReaderRole', N'SQLAgentOperatorRole' ) ORDER BY roles.name, members.name; |
msdb high-privilege database roles
The Agent roles are not the only concern. A user with db_owner or broad DDL rights in msdb can often change objects that Agent jobs depend on. Review fixed database role membership and explicit grants.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
USE msdb; GO SELECT roles.name AS role_name, members.name AS member_name, members.type_desc AS member_type FROM sys.database_role_members AS drm JOIN sys.database_principals AS roles ON roles.principal_id = drm.role_principal_id JOIN sys.database_principals AS members ON members.principal_id = drm.member_principal_id WHERE roles.name IN ( N'db_owner', N'db_ddladmin', N'db_securityadmin', N'db_accessadmin' ) ORDER BY roles.name, members.name; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
USE msdb; GO SELECT USER_NAME(dp.grantee_principal_id) AS grantee_name, dp.state_desc, dp.permission_name, dp.class_desc, OBJECT_SCHEMA_NAME(dp.major_id) AS object_schema, OBJECT_NAME(dp.major_id) AS object_name FROM sys.database_permissions AS dp WHERE dp.grantee_principal_id NOT IN (0, 1, 2) AND dp.permission_name IN ( N'ALTER', N'CONTROL', N'EXECUTE', N'IMPERSONATE', N'TAKE OWNERSHIP', N'VIEW DEFINITION' ) ORDER BY grantee_name, permission_name; |
SQL Agent job ownership and dangerous subsystems
Job ownership is security-relevant. A job owned by a sysadmin runs with elevated behavior in many paths, while a job owned by a normal user may fail or behave differently. The risky case is not only the owner itself; it is a privileged owner combined with weaker modification control.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
USE msdb; GO SELECT j.name AS job_name, SUSER_SNAME(j.owner_sid) AS job_owner, j.enabled, j.date_created, j.date_modified FROM dbo.sysjobs AS j WHERE SUSER_SNAME(j.owner_sid) NOT IN ( N'sa', N'DOMAIN\ApprovedSqlAgentOwners' ) ORDER BY j.date_modified DESC; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
USE msdb; GO SELECT j.name AS job_name, SUSER_SNAME(j.owner_sid) AS job_owner, s.step_id, s.step_name, s.subsystem, s.proxy_id, p.name AS proxy_name, s.database_name, LEFT(s.command, 4000) AS command_sample FROM dbo.sysjobsteps AS s JOIN dbo.sysjobs AS j ON j.job_id = s.job_id LEFT JOIN dbo.sysproxies AS p ON p.proxy_id = s.proxy_id WHERE s.subsystem IN (N'CmdExec', N'PowerShell', N'SSIS', N'ActiveScripting') OR s.proxy_id IS NOT NULL ORDER BY j.name, s.step_id; |
Job changes and failed execution probes
SQL Server Agent history is not a complete security log, but is still useful for spotting abnormal behavior. Look for recent modifications, job failures after permission errors, and unexpected subsystem usage.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
USE msdb; GO SELECT TOP (200) j.name AS job_name, h.step_id, h.step_name, h.run_date, h.run_time, h.run_status, h.sql_message_id, h.sql_severity, LEFT(h.message, 2000) AS message FROM dbo.sysjobhistory AS h JOIN dbo.sysjobs AS j ON j.job_id = h.job_id WHERE h.run_status <> 1 ORDER BY h.instance_id DESC; |
SQL Audit events for msdb
Use SQL Server Audit for durable records of permission and object changes. At minimum, consider auditing role membership changes, database permission changes, schema/object changes in msdb, backup/restore operations, and server-level permission changes. Tune audit targets and filters for your environment so the output is reviewable.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
/* Example pattern - adapt file paths, retention, and filters to your standard. */ USE master; GO CREATE SERVER AUDIT [Audit_SQLServer_Security] TO FILE (FILEPATH = N'D:\SQLAudit\', MAXSIZE = 1024 MB, MAX_ROLLOVER_FILES = 20) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE); GO ALTER SERVER AUDIT [Audit_SQLServer_Security] WITH (STATE = ON); GO CREATE SERVER AUDIT SPECIFICATION [ServerAudit_SecurityBoundaryChanges] FOR SERVER AUDIT [Audit_SQLServer_Security] ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP), ADD (SERVER_PERMISSION_CHANGE_GROUP), ADD (DATABASE_CHANGE_GROUP), ADD (BACKUP_RESTORE_GROUP) WITH (STATE = ON); GO USE msdb; GO CREATE DATABASE AUDIT SPECIFICATION [DBAudit_msdb_Changes] FOR SERVER AUDIT [Audit_SQLServer_Security] ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP), ADD (DATABASE_PERMISSION_CHANGE_GROUP), ADD (SCHEMA_OBJECT_CHANGE_GROUP), ADD (DATABASE_OBJECT_CHANGE_GROUP), ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP) WITH (STATE = ON); GO |
Recommended fixes and mitigations
Restrict Agent role membership
Only trusted DBAs and operational service accounts should be members of SQLAgentUserRole, SQLAgentReaderRole, or SQLAgentOperatorRole. Treat these roles as administrative.
Separate job ownership from job editing
Privileged jobs should have standardized owners, but lower-privileged users shouldn’t be able to modify them, the procedures they call, or tables they trust.
Control proxies and credentials
Baseline job definitions
Export job definitions to source control or a signed configuration baseline. Alert when job steps, schedules, owners, proxies, or command text change.
Avoid user-writable command queues
If jobs read work from tables, validate ownership, permissions, triggers, and input sanitization. Don’t let low-privileged users write directly into privileged job control tables.
Protect msdb
Never grant db_owner in msdb to application teams or vendors. Only grant the minimum Agent capability required, and review it regularly.
Protect your data. Demonstrate compliance.
Trigger-aware maintenance: when trusted jobs run untrusted code (and why privileged maintenance jobs can be hijacked through triggers)
Triggers are a classic example of code that runs because something else happened. It’s useful for auditing and enforcement but creates a permission-hijacking risk.
A DML trigger fires when a table is modified. A DDL trigger fires when a matching DDL event occurs. In many cases, the trigger executes in the security context of the statement that fired it, rather than the user who originally created the trigger.
This means a lower-privileged database owner, developer, or compromised application account may be able to create a trigger that waits for a privileged DBA or Agent job to touch the database. When the privileged maintenance process runs ALTER INDEX, UPDATE STATISTICS, certain schema changes, or data cleanup, the trigger fires in that privileged context.
Backup and DBCC CHECKDB aren’t the trigger-hijacking examples most DBAs should worry about. The more relevant operations are the ones that issue DDL or DML inside user databases, especially index maintenance, statistics maintenance, ETL cleanup, replication cleanup, CDC enable/disable operations, and ad-hoc remediation scripts.
An important distinction
The problem is not that triggers are always unsafe. Rather, the issues begin when privileged code touches objects or databases that lower-privileged users can influence. The maintenance process then becomes a bridge between two trust levels.
Signs a DBA should look for
- DDL triggers in user databases where local
db_ownerusers are not trusted as server administrators.
- Server-level DDL triggers not being documented in the change-management process.
- DML (data manipulation language) triggers on tables touched by privileged SQL Agent jobs, replication jobs, CDC (change data capture) processes, ETL (extract, transform, load) cleanup, or maintenance scripts.
- Triggers created or modified shortly before scheduled maintenance windows.
- Triggers that contain dynamic SQL, server-level statements,
GRANT/ALTER SERVER ROLE, linked server calls, xp_cmdshell, OLE Automation, SQL Agent procedure calls, or cross-database access.
- Maintenance plans that can’t run under a lower-privileged execution context, or can’t wrap operations in
EXECUTE AS USER.
- Don’t run broad maintenance jobs as sysadmin when they touch databases controlled by local owners, unless the workflow has been reviewed and sandboxed.
- For maintenance inside untrusted databases, use an execution pattern that prevents server-level permission hijacking, such as executing the database-local work under a contained database context (exec as user dbo) where appropriate.
- Review maintenance tools and parameters. For example, maintenance frameworks that support an
EXECUTE ASoption can help reduce the risk of a sysadmin token being hijacked by a trigger.
- Remove unnecessary
db_ownerand broadALTERpermissions from application and vendor accounts. Use custom roles with explicit object-level or schema-level grants.
- Before privileged migrations or manual DDL against an untrusted database, carry out an inventory on triggers – and consider disabling non-essential triggers only through a controlled change process.
- Document which jobs are expected to touch which databases. Alert when a job starts touching a new database, schema, or object class.
What to monitor and how
Inventory database and server triggers
Inventory is the first control. You can’t protect a privileged maintenance job from untrusted triggers if you don’t know which triggers actually exist.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
/* Run in each user database. */ SELECT DB_NAME() AS database_name, t.name AS trigger_name, t.parent_class_desc, OBJECT_SCHEMA_NAME(t.parent_id) AS parent_schema, OBJECT_NAME(t.parent_id) AS parent_object, t.is_disabled, t.create_date, t.modify_date, OBJECT_DEFINITION(t.object_id) AS trigger_definition FROM sys.triggers AS t ORDER BY t.modify_date DESC; |
|
1 2 3 4 5 6 7 8 9 10 |
/* Server-level DDL triggers. */ SELECT name AS trigger_name, parent_class_desc, is_disabled, create_date, modify_date, OBJECT_DEFINITION(object_id) AS trigger_definition FROM sys.server_triggers ORDER BY modify_date DESC; |
Look for suspicious trigger content
Run the following in each user database. Adjust patterns to your standards.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SELECT DB_NAME() AS database_name, t.name AS trigger_name, t.parent_class_desc, t.is_disabled, t.modify_date, m.definition FROM sys.triggers AS t JOIN sys.sql_modules AS m ON m.object_id = t.object_id WHERE m.definition LIKE N'%ALTER SERVER ROLE%' OR m.definition LIKE N'%CONTROL SERVER%' OR m.definition LIKE N'%sp_addsrvrolemember%' OR m.definition LIKE N'%xp_cmdshell%' OR m.definition LIKE N'%sp_start_job%' OR m.definition LIKE N'%EXEC%(%' OR m.definition LIKE N'%sp_executesql%' OR m.definition LIKE N'%AT [%' -- linked server execution pattern OR m.definition LIKE N'%OPENQUERY%' ORDER BY t.modify_date DESC; |
Audit trigger creation and modification
For SQL Audit, database-level object and schema change groups can capture many trigger-related DDL events. In environments with high volume, scope the audit to high-risk databases or filter downstream in the SIEM.
|
1 2 3 4 5 6 7 8 |
/* Database audit specification pattern for high-risk user databases. */ CREATE DATABASE AUDIT SPECIFICATION [DBAudit_DDL_And_Trigger_Changes] FOR SERVER AUDIT [Audit_SQLServer_Security] ADD (SCHEMA_OBJECT_CHANGE_GROUP), ADD (DATABASE_OBJECT_CHANGE_GROUP), ADD (DATABASE_PERMISSION_CHANGE_GROUP) WITH (STATE = ON); GO |
Extended Events can also be used to observe DDL and object-alteration activity.
The exact event set should be tested on your SQL Server version and workload, but a practical starting point is capturing object_created, object_altered, object_deleted, and ddl_database_level_events, with sql_text, database_name, username, and client_hostname actions.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
CREATE EVENT SESSION [XE_DDL_Trigger_Object_Changes] ON SERVER ADD EVENT sqlserver.object_created ( ACTION(sqlserver.client_hostname, sqlserver.database_name, sqlserver.server_principal_name, sqlserver.sql_text) ), ADD EVENT sqlserver.object_altered ( ACTION(sqlserver.client_hostname, sqlserver.database_name, sqlserver.server_principal_name, sqlserver.sql_text) ), ADD EVENT sqlserver.object_deleted ( ACTION(sqlserver.client_hostname, sqlserver.database_name, sqlserver.server_principal_name, sqlserver.sql_text) ) ADD TARGET package0.event_file ( SET filename = N'D:\XE\XE_DDL_Trigger_Object_Changes.xel', max_file_size = 100, max_rollover_files = 10 ) WITH (STARTUP_STATE = ON); GO ALTER EVENT SESSION [XE_DDL_Trigger_Object_Changes] ON SERVER STATE = START; GO |
Correlate trigger changes with maintenance windows
The most useful detection is often correlation, not a single event. A trigger created at 01:55 followed by an index maintenance job at 02:00, for example, is more suspicious than either event alone. Store job schedules, job runs, trigger create/modify times, and DDL audit records in a central place, and alert on proximity.
|
1 2 3 4 5 6 7 8 9 10 11 |
/* Example: recent triggers modified in a maintenance-sensitive window. */ SELECT DB_NAME() AS database_name, t.name AS trigger_name, t.parent_class_desc, t.create_date, t.modify_date, DATEDIFF(MINUTE, t.modify_date, SYSDATETIME()) AS minutes_since_modify FROM sys.triggers AS t WHERE t.modify_date >= DATEADD(HOUR, -24, SYSDATETIME()) ORDER BY t.modify_date DESC; |
Recommended fixes and mitigations
Restrict trigger creation
Do not grant db_owner or broad ALTER permissions to users who shouldn’t be able to influence privileged maintenance.
Inventory before privileged maintenance
Before running sensitive DDL/DML in an untrusted database, inventory triggers and disable unverified triggers where the operational risk allows it.
Use a sandboxed execution context
For maintenance inside databases with untrusted owners or developers, consider running DDL/DML under EXECUTE AS USER = 'dbo', in order to prevent server-level token hijacking. Validate this pattern in your environment, especially if TRUSTWORTHY is ON.
Avoid SSMS maintenance plans for sensitive untrusted databases
If you need explicit execution-context control, use scripts or maintenance frameworks that support an execution user parameter.
Treat trigger changes as security events
A new or modified trigger in a production database should be visible to DBAs, security operations, or both.
Untrusted restores: why a backup file is not just data, and why restore workflows are a security boundary
A SQL Server backup carries database metadata, owners, SIDs, modules, triggers, assemblies, Service Broker configuration, replication artifacts, CDC objects, permissions, schemas, views, indexed views, and many other pieces of executable or security-relevant state.
Microsoft documentation is very explicit when it states that restoring a backup from an untrusted source is a security risk. It goes on to say that a malicious backup can compromise the SQL Server environment, and can introduce arbitrary code execution before validation scripts have a chance to run.
For DBAs, the takeaway is simple: an external .bak file should be treated more like untrusted software than like a CSV file.
This matters because restores are routine. DR (disaster recovery) tests, production-to-staging refreshes, vendor troubleshooting, migrations, client data imports, and QA (quality assurance) refreshes all create pressure to restore quickly. And it’s here – the drive for convenience – where trust-boundary mistakes can and often do happen.
Practical attack paths to think about defensively
Ownership smuggling
If the owner SID (security identifier) embedded in the backup maps to an existing privileged login on the target instance, the restored database may arrive owned by that principal. If the SID doesn’t map, ownership may fall to the login performing the restore. Both cases are security-relevant.
Persisted module and trigger state
Stored procedures, triggers, functions, and views can arrive with code that later runs under a more privileged workflow.
Indexed-view and metadata trust
SQL Server validates indexed-view rules at creation time. A restored database brings persisted metadata from another environment, so DBAs shouldn’t assume all restored metadata was produced under the target instance’s trust model.
Replication, CDC, Service Broker, and CLR artifacts
These features can carry operational state that interacts with privileged jobs, activation, cleanup, or external execution surfaces.
Post-restore automation
The first risky moment is the restore itself – and then the automation that immediately follows. This includes compatibility fixes, user mapping scripts, index maintenance, statistics updates, ETL validation, and smoke tests.
Signs a DBA should look for
- Backups restored from vendors, customers, developers, unknown file shares, email attachments, ticket uploads, or cloud storage locations outside your trusted backup chain.
- Restored databases owned by
sa, a deployment account, a DBA’s personal login, a sysadmin login, or an unexpected application login.
TRUSTWORTHY ON,DB_CHAINING ON, enabled Service Broker queues, activation procedures, CLR assemblies, or external access assemblies in newly restored databases.
- Unexpected replication, CDC, change tracking, DDL triggers, DML triggers, database-level permissions, orphaned users, or high-privilege database roles after restore.
- Post-restore jobs that immediately run maintenance or validation code before security inspection is complete.
- Restore operations performed by highly privileged personal accounts rather than controlled automation accounts.
What to monitor and how
Audit backup and restore commands
Enable SQL Server Audit for BACKUP_RESTORE_GROUP at server scope. This gives you a durable log of backup and restore commands. Pair it with Windows, storage, and change-management logs so you can trace where the backup file came from.
|
1 2 3 4 5 |
CREATE SERVER AUDIT SPECIFICATION [ServerAudit_BackupRestore] FOR SERVER AUDIT [Audit_SQLServer_Security] ADD (BACKUP_RESTORE_GROUP) WITH (STATE = ON); GO |
Post-restore security gate
Every restore into a shared or production-adjacent environment should pass through a security gate before application users or maintenance jobs touch it. The following checks are examples of the baseline state to capture immediately after restore.
Instance-level view of newly restored database properties:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT d.name AS database_name, SUSER_SNAME(d.owner_sid) AS owner_name, d.owner_sid, d.is_trustworthy_on, d.is_db_chaining_on, d.is_broker_enabled, d.is_cdc_enabled, d.create_date, d.compatibility_level FROM sys.databases AS d WHERE d.database_id > 4 ORDER BY d.create_date DESC; |
What to run inside the restored database:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT USER_NAME(drm.role_principal_id) AS role_name, USER_NAME(drm.member_principal_id) AS member_name FROM sys.database_role_members AS drm WHERE USER_NAME(drm.role_principal_id) IN ( N'db_owner', N'db_securityadmin', N'db_ddladmin', N'db_accessadmin', N'db_backupoperator' ) ORDER BY role_name, member_name; |
Modules that execute under a specific context:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT SCHEMA_NAME(o.schema_id) AS schema_name, o.name AS object_name, o.type_desc, m.execute_as_principal_id, USER_NAME(m.execute_as_principal_id) AS execute_as_user FROM sys.sql_modules AS m JOIN sys.objects AS o ON o.object_id = m.object_id WHERE m.execute_as_principal_id IS NOT NULL ORDER BY schema_name, object_name; |
Triggers and enabled status:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT DB_NAME() AS database_name, t.name AS trigger_name, t.parent_class_desc, OBJECT_SCHEMA_NAME(t.parent_id) AS parent_schema, OBJECT_NAME(t.parent_id) AS parent_object, t.is_disabled, t.create_date, t.modify_date FROM sys.triggers AS t ORDER BY t.modify_date DESC; |
Feature and artifact review
CDC status:
|
1 2 3 |
SELECT DB_NAME() AS database_name, is_cdc_enabled FROM sys.databases WHERE name = DB_NAME(); |
Change tracking:
|
1 2 3 |
SELECT DB_NAME() AS database_name, is_auto_cleanup_on, retention_period, retention_period_units_desc FROM sys.change_tracking_databases WHERE database_id = DB_ID(); |
Service Broker queues with activation:
|
1 2 3 4 5 6 7 8 |
SELECT SCHEMA_NAME(q.schema_id) AS schema_name, q.name AS queue_name, q.is_activation_enabled, q.activation_procedure, q.execute_as_principal_id FROM sys.service_queues AS q WHERE q.is_activation_enabled = 1; |
Assemblies:
|
1 2 3 |
SELECT name, permission_set_desc, is_user_defined FROM sys.assemblies WHERE is_user_defined = 1; |
Recommended fixes and mitigations
Create a restore quarantine tier
External or untrusted backups should land first on isolated SQL Server instances with no production trust, no linked servers to sensitive environments, no domain privileges beyond what is required, and no automatic maintenance jobs.
Use a controlled restore account
Do not restore untrusted backups with personal sysadmin accounts if automation can use a controlled account and record provenance.
Normalize ownership immediately
After restore, change the database owner to a dedicated low-use administrative owner approved by your policy. Avoid leaving it owned by a personal login or unexpected mapped SID.
Disable risky database properties
Set TRUSTWORTHY OFF and DB_CHAINING OFF unless a documented exception exists.
Inspect before touching
Never run index maintenance, update statistics, ETL validation, smoke tests, or application traffic until triggers, modules, Service Broker activation, CLR (Common Language Runtime), replication, CDC, permissions, and owners have been reviewed.
Preserve evidence
Record source path, checksum, restore operator, restore time, database owner, DB_CHAINING, and a pre-sanitization inventory before making changes.
Prefer data-only imports when possible
For truly untrusted data, importing data into pre-created schemas is often safer than restoring an entire database with executable metadata.
Subscribe to the Simple Talk newsletter
Cross-database ownership chaining and shared owners: why shared ownership can bypass the permission model you thought you had
Cross-database ownership chaining is a compatibility feature that can bypass permission checks across databases when ownership aligns.
It exists because some applications were built around modules in one database reading objects in another without explicit grants. The security cost is that the permission boundary between databases becomes much less clear.
You can read a lot more about cross-database ownership chaining in my dedicated deep-dive guide here.
The common risky pattern is simple: many databases are owned by sa, the same deployment login, or the same application owner. Further, DB_CHAINING is enabled, and modules reference objects across database boundaries.
As such, DBAs assume each database is isolated because users were only granted access to one database – but ownership chaining can make that assumption false.
This becomes even more difficult when tempdb or global temporary objects are used as workflow glue, as tempdb is shared and global temporary tables are visible across sessions. If privileged jobs use predictable global temporary table names, or trust data placed in shared temporary structures, a low-privileged session may be able to race, poison, or observe parts of the workflow.
Signs a DBA should look for
- The server-level cross db ownership chaining option is enabled.
- User databases have
is_db_chaining_on = 1without a documented exception.
- Many unrelated databases share the same owner, especially
saor a broad deployment login.
TRUSTWORTHY ONappears in user databases, especially when those databases are owned by sysadmin principals.
- Stored procedures, views, functions, triggers, or jobs reference three-part names across databases.
- Agent jobs or application code use predictable global temporary tables such as ##WorkQueue, ##MaintenanceQueue, or ##Results.
- Users who are powerful in one database can influence tables or modules consumed by code in another database.
What to monitor and how
Server and database configuration
The server-level option:
|
1 2 3 4 5 |
SELECT name, value_in_use FROM sys.configurations WHERE name = N'cross db ownership chaining'; |
Database-level chaining/trust/ownership:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT name AS database_name, SUSER_SNAME(owner_sid) AS owner_name, is_db_chaining_on, is_trustworthy_on, is_broker_enabled, is_cdc_enabled FROM sys.databases WHERE database_id > 4 AND (is_db_chaining_on = 1 OR is_trustworthy_on = 1) ORDER BY name; |
Shared owner review
|
1 2 3 4 5 6 7 8 9 |
SELECT SUSER_SNAME(owner_sid) AS owner_name, COUNT(*) AS database_count, STRING_AGG(CONVERT(nvarchar(max), name), N', ') AS databases FROM sys.databases WHERE database_id > 4 GROUP BY owner_sid HAVING COUNT(*) > 1 ORDER BY database_count DESC; |
Cross-database references in modules
The following simple pattern isn’t perfect, but is still a useful starting point for finding modules that appear to use three-part names. Combine it with code review and dependency data, and run it in each database:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT DB_NAME() AS database_name, SCHEMA_NAME(o.schema_id) AS schema_name, o.name AS object_name, o.type_desc, o.modify_date, LEFT(m.definition, 4000) AS definition_sample FROM sys.sql_modules AS m JOIN sys.objects AS o ON o.object_id = m.object_id WHERE m.definition LIKE N'%].[%].[%' OR m.definition LIKE N'%..%' OR m.definition LIKE N'%OPENQUERY%' OR m.definition LIKE N'%EXECUTE% AT %' ORDER BY o.modify_date DESC; |
Global temporary objects used by non-admin sessions
Global temporary object monitoring is usually best handled with Extended Events or a server-side collector. Focus on creation of objects named ##% in tempdb, especially near maintenance windows or by unexpected logins.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
CREATE EVENT SESSION [XE_GlobalTempObjects] ON SERVER ADD EVENT sqlserver.object_created ( ACTION(sqlserver.client_hostname, sqlserver.database_name, sqlserver.server_principal_name, sqlserver.session_id, sqlserver.sql_text) WHERE ([database_name] = N'tempdb') ), ADD EVENT sqlserver.object_deleted ( ACTION(sqlserver.client_hostname, sqlserver.database_name, sqlserver.server_principal_name, sqlserver.session_id, sqlserver.sql_text) WHERE ([database_name] = N'tempdb') ) ADD TARGET package0.event_file ( SET filename = N'D:\XE\XE_GlobalTempObjects.xel', max_file_size = 100, max_rollover_files = 10 ) WITH (STARTUP_STATE = ON); GO ALTER EVENT SESSION [XE_GlobalTempObjects] ON SERVER STATE = START; GO |
If your SQL Server version or XE metadata doesn’t expose object names in the event payload as expected, collect sql_text and session context, then correlate them with statements that create ## objects. Test this session in a non-production environment before standardizing it.
Future-proof database monitoring with Redgate Monitor
Recommended fixes and mitigations
Keep cross-database ownership chaining disabled
The server option should remain off. Database-level DB_CHAINING should be an exception with owner approval, documented business justification, and compensating controls.
Stop sharing powerful owners by default
Don’t make every database owned by sa just for convenience. Instead, use dedicated owner logins that aren’t used for application connections and don’t hold unnecessary server privileges.
Prefer module signing
When a module in one database must access another database, certificate signing can grant the exact permission to the module without enabling broad cross-database trust.
Use explicit permissions where possible
A clear GRANT is easier to review and audit than an implicit ownership chain.
Refactor global temp object workflows
Privileged jobs should prefer local temporary tables, table variables, or permanent staging tables in locked-down schemas. Avoid predictable ## names for security-sensitive workflows.
Review after restores and migrations
Restores can introduce owner alignment, DB_CHAINING, and cross-database module references that were not present in the target environment before.
A practical DBA checklist for you to follow
The following checklist is intended to be operational. It can be turned into a monthly security review, a health check, or a set of monitoring rules.
| Control | Minimum check | Suggested frequency |
| SQL Agent roles | Review SQLAgentUserRole,SQLAgentReaderRole, and SQLAgentOperatorRole membership in msdb. | Daily or weekly |
msdb privileged roles | Review db_owner, db_ddladmin, db_securityadmin, and explicit ALTER/CONTROL/IMPERSONATE permissions. | Weekly |
| Job ownership | List jobs not owned by approved owner accounts; review dangerous subsystems and proxies. | Daily or weekly |
| Job changes | Audit or diff job steps, schedules, owners, proxies, and command text. | Continuous |
| Triggers | Inventory server/database triggers; alert on create/alter/drop; inspect high-risk trigger text. | Continuous plus pre-maintenance |
| Restore activity | Audit BACKUP_RESTORE_GROUP; capture source path and operator. | Continuous |
| Post-restore gate | Check owner_sid, DB_CHAINING, CDC, replication, CLR, triggers, modules, roles. | Every restore |
| Cross-db ownership | Review server option, database DB_CHAINING, shared owners, cross-database module references. | Weekly or monthly |
| Global temp workflows | Identify privileged jobs using ## objects or shared tempdb coordination. | Monthly and during code review |
| Exceptions | Document every exception with owner, business reason, expiry date, and compensating controls. | Every change |
Conclusion
The most dangerous SQL Server exposures are often trust-boundary failures rather than specific, isolated bugs. A normal Agent job, a normal restore, a normal trigger, or a normal cross-database module can become an attack path when a lower-trust principal can influence something that higher-trust automation later executes.
For DBAs, the call to action isn’t to simply disable every feature. Many of them – such as SQL Server Agent, restores, triggers, CDC, replication, Service Broker, and cross-database modules – have legitimate uses.
Instead, it’s about making trust explicit. At all times, you need to know who can modify what, know which workflows execute under privileged contexts, be aware of audit changes, and avoid allowing untrusted databases or users to become part of privileged automation.
Summary: the urgent actions you should take to secure your SQL Server
Validate msdb, baseline SQL Agent, quarantine untrusted restores, normalize database ownership, disable unnecessary trust properties, inventory triggers, and replace broad ownership chains with explicit, reviewable permissions.
FAQs: MSDB attack paths: how to secure SQL Server Agent, triggers, and restores
1. What is msdb in SQL Server, and why does it matter for security?
Msdb is the system database that stores SQL Server Agent jobs, schedules, proxies, credentials, and backup/restore history. Because it controls automated, often privileged, execution, a misconfigured or overly permissive msdb can let a low-privileged user influence what a high-privileged job runs.
2. How can a SQL Server Agent job be exploited without sysadmin access?
Through indirect execution: a lower-privileged user modifies a job step, called procedure, referenced table, or proxy that a privileged job later runs. The job itself executes the change on the attacker’s behalf during its next scheduled run.
3. Why is restoring a backup from an untrusted source risky?
A backup file contains more than data – it can include triggers, stored procedures, CLR assemblies, Service Broker configuration, and ownership metadata. Restoring it can introduce executable objects that later run under a privileged context before anyone reviews them.
4. Can a database trigger really hijack a SQL Server Agent job?
Yes. Many triggers execute in the security context of whoever’s action fired them. If a lower-privileged user creates a trigger on a table touched by a privileged maintenance job, that trigger can run with the job’s elevated privileges.
5. What is cross-database ownership chaining, and why is it a risk?
It’s a legacy compatibility feature that can bypass permission checks between databases when they share the same owner. If many databases are owned by the same privileged login, access granted in one database can silently extend into another.
6. What are the first steps to reduce these risks?
Restrict SQLAgentUserRole/ReaderRole/OperatorRole and db_owner membership in msdb, audit job ownership and proxies, inventory triggers before privileged maintenance, quarantine untrusted restores, and keep cross-database ownership chaining disabled.
This document contains proprietary information and is protected by copyright law.
Copyright © 2026 Red Gate Software Limited. All rights reserved
Load comments